Database Schema (DB)

The database schema describes how data is stored in tables in a concrete relational database system (RDBMS). This model should create the Data Definition Language (DDL) for realization on a concrete database. The standard language for DDL is SQL.

Use the following model elements when modeling the database schema:

• Database Diagram

  The database diagram shows a submodel in the database schema. Database tables are shown as nodes and foreign keys as edges.

  Innovator uses the IDEF1X notation.

  Database views can also be displayed as nodes and their From clauses as edges.

• Database Table

  Table of a relational database system.

  SQL: CREATE TABLE

• Table Column

  Column of a database table which is assigned a data type to save an instance's property value.

  SQL: CREATE TABLE (< column definition>,...)

• Database Table's Key

  A database table's primary or alternative key which is referenced by a foreign key. A key is a selection of table columns; their values can be used to uniquely identify an instance.

  SQL: ALTER TABLE ADD PRIMARY KEY or ALTER TABLE ADD CONSTRAINT UNIQUE

• Foreign Key

  A foreign key references a key. The foreign key's instance is linked with the key's instance. The foreign key contains a suitable foreign key column for each of the referenced key's columns.

  SQL: OLD TABLE ADD FOREIGN KEY ... REFERENCES

• SQL Check Constraint

  An SQL check constraint stores values of table columns which can be stored.

  SQL: CREATE TABLE ( <column definition>, CHECK (<constraint>) ...)

• Constraint (Disjunctive Tables)

  This constraint is used for modeling disjunctive instance sets in tables which have a common primary key pool. This enables an equivalent statement to be modeled to show how they are modeled in the ER model using generalization sets.

  Constrained elements can be database tables and foreign keys. The affected tables are also shown as sister tables. No foreign key is created between sister tables for denormalization.

• Index

  An index is a selection of table columns which makes it quicker to access instances. Uniqueness can also be set at the same time.

  SQL: CREATE [UNIQUE] INDEX

• Index Columns

  The use of table columns in an index. An index column can still modify the columns used with a function expression.

  CREATE INDEX (<column>) or CREATE INDEX (<Function>(<column>))

• Database Trigger

  A trigger propagates execution of DML actions (insert, delete, update) to table-internal and table-external data. Depending on the time of execution, it is possible to e.g. determine values before insertion or maintain redundancies at a later stage. Creating a trigger ensures that the database system carries out the correct maintenance of data.

  SQL: CREATE TRIGGER

• Database View

  A database view combines the columns of one or more tables in a table. This may mean that a database view's columns contain redundant data.

  The term 'column set' will be used as an umbrella term for database tables or database views throughout this help.

  SQL: CREATE VIEW

• Database View Column

  The values of view columns are obtained from values of the columns used. This means that it is also possible for calculation expressions with functions to be used.

  SQL: CREATE VIEW (<View column definition>)

• From Clause

  A database view contains one or more From clauses which determine which column set the view's content is taken from. The columns of these column sets are used for calculating the view column's value.

  SQL CREATE VIEW .... FROM <table>, ...

• Stored Procedure

  This element is used for storing procedures.

  This procedure is normally implemented using tools which belong to a concrete database product which uses a database realized with DDL.

  SQL: CREATE PROCEDURE or CREATE FUNCTION

• Stored Procedure Expression

  Each stored procedure can only contain one implementation for various concrete databases which was implemented in the appropriate language. A configured type system is needed to exist.

• DB Options

  These options are required for generated complete DDL scripts. The available options are configured for the type system used (RDBMS).

• Database Object Group

  Is a specialization of the package which has additional properties of an SQL object (see below).

• DB User Management

  User, group, role, privileges and role authorizations for RDBMS can be modeled.

  SQL: GRANT | REVOKE <actions> ON ... TO <user|group|role> AS <user>

• Denormalization

  When tables are merged, an element of the "Table Added" class is created in the modified table; it logs the denormalization. This sets the direction of the merge. The element contains the name of the added table and also inherits its dependencies and the foreign key's dependencies which the denormalization was implemented via.

  A number can be entered when merging tables to primary keys. One "Instance of table added" and a copy of the added table's columns are created respectively and linked with each other.

• Generic SQL

  The Innovator metamodel does not have the same power as the SQL language scope. The "Generic SQL" element type is used for tailoring models. Stereotypes and configurations of relationships enable use as a special SQL element (see SQL object).

• SQL Object

  SQL object is an umbrella term for some of the elements listed above with the following properties. Privileges can be assigned to an SQL object. SQL objects can be linked with each other if the "SQL Objects Used" and "SQL Objects to be Used" relationships are configured.

  The following elements are SQL objects

  • Database object group

  • Database table

  • Database view

  • Table column

  • View column

  • Key

  • Foreign Key

  • Index

  • SQL check constraint

  • Stored procedure

  • Trigger

  • Generic SQL